/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tesam.gedeon.dao.impl;

import com.tesam.gedeon.controller.Secret;
import com.tesam.gedeon.dao.SystemUserDao;
import com.tesam.gedeon.entity.SystemUser;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Sistema
 */
@Repository
public class SystemUserDaoImpl extends SimpleJdbcDaoSupport implements SystemUserDao{

    @Autowired
    public SystemUserDaoImpl(DataSource dataSource) {
        setDataSource(dataSource);
    }
    
    @Override
    public int save(SystemUser User) {
       throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
 
    @Override
    public int delete(int id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public int update(SystemUser User) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public SystemUser getById(int id) {
        try {
             return getSimpleJdbcTemplate().queryForObject("SELECT * FROM systemuser WHERE id = ? ", new BeanPropertyRowMapper<SystemUser>(SystemUser.class), id);
        } catch (Exception e) {
            System.out.println("Exception:" + e.getMessage());
        }
      return null;
    }

    @Override
    public List<SystemUser> getAll() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<SystemUser> getAllByLimit(int startLimit, int endLimit, String dataToSort, String directionOfOrder) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<SystemUser> searchByName(SystemUser User, int startLimit, int endLimit) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public void updateSession(SystemUser User){
        
      int result = 0 ;  
       
      result = getJdbcTemplate().queryForInt("select count(*) as total from  session where session_id = ?", 
                User.getSessionId());
       
      
      if(result == 0){
      getJdbcTemplate().update("INSERT INTO session"
                + "("
                + "user_id,start_time,start_date , computer_ip , session_id , end_time , end_date "
                + ")VALUES(? , NOW() , NOW() , ? , ? , NOW() , NOW() )", User.getId() , User.getIpComputer() , User.getSessionId() );
      }else{
      
      getJdbcTemplate().update("update session set end_time = NOW() , end_date = NOW() "
              + " where session_id =  ?" , User.getSessionId());
      }
        
    }
    
    @Override
    public void logout(SystemUser User){
    
        getJdbcTemplate().update("update session set end_time = NOW() , end_date = NOW() "
                                + " , logout = 1  where session_id = ? " , User.getSessionId());
        
    }
    
    @Override
    public List<SystemUser> getModule(SystemUser User){
    List<SystemUser> result = new ArrayList();
         try{
           result =  getSimpleJdbcTemplate().query(" select id  , name as moduleName , moduleparentid as moduleParentId  "
                   + "from module where moduleparentid > 0 ",
                new BeanPropertyRowMapper<SystemUser>(SystemUser.class));
  
         }catch(Exception e){
            System.out.println("getModule : "+e.getMessage());
         }
         return result ;
       }
    
    @Override
    public List<SystemUser> getModuleSearch(SystemUser User){
        List<SystemUser> result = new ArrayList();
            try{
              result =  getSimpleJdbcTemplate().query(" select id  , name as moduleName , moduleparentid as moduleParentId  "
                   + "from module where moduleparentid > 0 and name like '%"+User.getModuleName()+"%' ",
                new BeanPropertyRowMapper<SystemUser>(SystemUser.class));
            }catch(Exception e){
                System.out.println("getModuleSearch : "+e.getMessage());
            }
        return result ;
    }
     
    @Override
    public List<SystemUser> getSearchModuleData(SystemUser User){
        List<SystemUser> result = new ArrayList();
        
            try{
                result =  getSimpleJdbcTemplate().query(" select id  , name as moduleName , moduleparentid as moduleParentId  "
                   + "from module where moduleparentid > 0 and name like '%"+User.getModuleName()+"%' ",
                new BeanPropertyRowMapper<SystemUser>(SystemUser.class));
            }catch(Exception e){
                System.out.println("getSearchModuleData : "+e.getMessage());
            }
        return result ;
    }
    
    @Override
    public List<SystemUser> getModuleData(SystemUser user){
    List<SystemUser> result = new ArrayList();
    
        try{ 
            result = getSimpleJdbcTemplate().query("select m.id , m.name as moduleName , m.moduleparentid ,  "
                    + "(select m2.name from module m2 where m2.id = m.moduleparentid) as parentTab "
                    + "from module m where m.id = ? " , 
                    new BeanPropertyRowMapper<SystemUser>(SystemUser.class) , user.getId() ) ;
        }catch(Exception e){
        System.out.println("getModuleData : " + e.getMessage());
        }
    
    return result ;
    }
    
   
    @Override
    public List<SystemUser> getParentTabSearch(SystemUser User){
        List<SystemUser> result = new ArrayList();
            try{
                result = getSimpleJdbcTemplate().query("select m.id , m.name as parentTab  " 
                        + "from module m where m.id in "
                        + "(select distinct(moduleparentid)  from module "
                        + "where name like '%"+User.getModuleName()+"%' ) ", 
                new BeanPropertyRowMapper<SystemUser>(SystemUser.class));
            }catch(Exception e){
                System.out.println("error : "+e.getMessage());
            }
        return result ;
    }
    
    @Override
    public List<SystemUser> getParentTab(){
    List<SystemUser> result = new ArrayList();
    
    try{
        result = getSimpleJdbcTemplate().query("select m.id , m.name as parentTab  " +
                                                "from module m where m.id in "
                                            + "(select distinct(moduleparentid)  from module ) ", 
                new BeanPropertyRowMapper<SystemUser>(SystemUser.class));
    }catch(Exception e){
    System.out.println("error : "+e.getMessage());
    }
    return result ;
    }
    
    @Override
    public List<SystemUser> getSession(SystemUser User){
    List<SystemUser> userSystem = new ArrayList();
    try{
        
        userSystem = getSimpleJdbcTemplate().query("select sys.userName  , sys.algo , "
            + " sys.id , sys.password ,sys.imagename , sys.name , sys.lastname , sys.email,  session_id as sessionId "
            + " from session se "
            + "inner join systemuser sys on sys.id = se.user_id "
            + "where logout = 0 and session_id = ? ", 
            new BeanPropertyRowMapper<SystemUser>(SystemUser.class), 
            User.getSessionId());
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
     
    return userSystem;
    }
    
    @Override
    public List<SystemUser> loginByCookie(SystemUser User){
         
        List<SystemUser> userSystem = new ArrayList();  
        try{
            System.out.println("SELECT username , algo , id , password , imagename  FROM systemuser WHERE username = "+User.getUserName());
        userSystem = getSimpleJdbcTemplate().query("SELECT username  , algo , id , password , imagename  FROM systemuser WHERE username = ?", 
                                                    new BeanPropertyRowMapper<SystemUser>(SystemUser.class), 
                                                    User.getUserName());
        if(userSystem.isEmpty()){
            return new ArrayList();
        }else{
            userSystem.get(0).setIpComputer(User.getIpComputer());
                        
            userSystem.get(0).setSessionId(User.getSessionId()); 
            
            this.updateSession(userSystem.get(0));
            
            System.out.println("connected by cookie");
        }
        
        }catch(Exception e){
        System.out.println("getSesssion error : "+e.getMessage());
        }
        return userSystem;
    }
    
    @Override
    public List<SystemUser> login(SystemUser User) { 
        Secret secret = new Secret();
        List<SystemUser> userSystem = new ArrayList();   
        try { 
        userSystem = getSimpleJdbcTemplate().query("SELECT username  , algo , id , password , imagename FROM systemuser WHERE username = ?", new BeanPropertyRowMapper<SystemUser>(SystemUser.class), User.getUserName());
           
         if(userSystem.size() > 0){
             
         String passwordGenerated = secret.getHmacMD5(User.getUserName().trim(), User.getPassword().trim(), userSystem.get(0).getAlgo().trim());
   
            if( passwordGenerated.equals(userSystem.get(0).getPassword().trim()) ){
                        userSystem.get(0).setIpComputer(User.getIpComputer());
                        
                        userSystem.get(0).setSessionId(User.getSessionId()); 
                        
                        System.out.println("Connected");
                        this.updateSession(userSystem.get(0)); 
                return userSystem;
                }else{
                System.out.println("no Connected");
                return new ArrayList();
            
                }
         }
         
            
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            Logger.getLogger(SystemUserDaoImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return userSystem;
        
    //return getJdbcTemplate().queryForInt("select count(*) as total from systemuser where username =  ?", User.getUserName());
    }
    
}

